package org.jawa.branch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.jawa.database.DbConnectionManager;
import org.jawa.user.User;
import org.jawa.user.UserAlreadyExistsException;
import org.jawa.user.UserEventDispatcher;
import org.jawa.user.UserNotFoundException;
import org.jawa.user.UserProvider;
import org.jawa.util.NotFoundException;
import org.jawa.util.cache.Cache;
import org.jawa.util.cache.CacheFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class BranchManager {
	private static final Logger Log = LoggerFactory.getLogger(BranchManager.class);

	// 单一实例.
	private static class BranchManagerContainer {
		private static BranchManager instance = new BranchManager();
	}

	/**
	 * 返回 BranchManager 的单一实例.
	 * 
	 * @return BranchManager 实例.
	 */
	public static BranchManager getInstance() {
		return BranchManagerContainer.instance;
	}

	private Cache<Long, Branch> branchCache;

	private BranchManager() {
		// 初始化缓存.
		branchCache = CacheFactory.createCache("Branch");
	}

	/**
	 * 创建新部门
	 * @param branch
	 * @return
	 * @throws Exception
	 */
	public Branch createBranch(Branch branch) throws Exception {
		try {
			loadBranchByBrNo(branch.getBrNo());
			throw new UserAlreadyExistsException("Branch BrNo  " + branch.getBrNo() + " already exists");
		} catch (NotFoundException unfe) {
			Connection con = null;
			PreparedStatement pstmt = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con
						.prepareStatement("INSERT INTO app_branch(br_id,br_no,br_name,br_status,br_parent_id) values(?,?,?,?,?) ");
				pstmt.setLong(1, branch.getBrId());
				pstmt.setString(2, branch.getBrNo());
				pstmt.setString(3, branch.getBrName());
				pstmt.setString(4, branch.getBrStatus());

				if (null != branch.getBrParentId()) {
					pstmt.setLong(5, branch.getBrParentId());
				} else {
					pstmt.setNull(5, Types.INTEGER);
				}
				pstmt.execute();
			} catch (Exception e) {
				Log.error("创建部门出错！", e);
				throw e;
			} finally {
				DbConnectionManager.closeConnection(pstmt, con);
			}

			branchCache.put(branch.getBrId(), branch);

			return branch;
		}
	}

	/**
	 *  更新部门的部份信息.
	 * @param userId
	 * @param brId
	 * @param loginNo
	 * @param userName
	 * @param userType
	 * @param userStatus
	 * @param userSex
	 * @param userPhone
	 * @param userEmail
	 * @param userPicture
	 * @return
	 * @throws Exception
	 */

	public void updateBranch(Long brId, String brNo, String brName, String brStatus, String brParentId)
			throws Exception {
		branchCache.remove(brId);
		
		if (null == brId) {
			throw new IllegalArgumentException("部门  brId 不可以为空！");
		}

		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE app_branch ");
			boolean first = true;
			if(StringUtils.isNotEmpty(brNo)){
				if(first){
					sql.append(" SET ");
					first = false;
				}
				sql.append("  br_no = '"+StringEscapeUtils.escapeSql(brNo)+"' ");
			}
			if(StringUtils.isNotEmpty(brName)){
				if(first){
					sql.append(" SET ");
					first = false;
				}else{
					sql.append(" , ");
				}
				sql.append("  br_name = '"+StringEscapeUtils.escapeSql(brName)+"' ");
			}
			if(StringUtils.isNotEmpty(brStatus)){
				if(first){
					sql.append(" SET ");
					first = false;
				}else{
					sql.append(" , ");
				}
				sql.append("  br_status = '"+StringEscapeUtils.escapeSql(brStatus)+"' ");
			}
			if(StringUtils.isNotEmpty(brParentId)){
				if(first){
					sql.append(" SET ");
					first = false;
				}else{
					sql.append(" , ");
				}
				sql.append("  br_parent_id = "+StringEscapeUtils.escapeSql(brParentId)+" ");
			}
			sql.append(" WHERE br_id = "+brId);
			con = DbConnectionManager.getConnection();
			pstmt = con
					.prepareStatement(sql.toString());
			pstmt.execute();
		} catch (Exception e) {
			Log.error("更新部门出错！", e);
			throw e;
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
		
	}

	/**
	 * 返回指定 brId 的部门.
	 * 
	 * @param userId
	 *            部门的 brId .
	 * @return br_Id 等于 <tt>brId</tt> 的部门.
	 * @throws NotFoundException
	 *             部门没有找到.
	 */
	public Branch getBranch(Long brId) throws NotFoundException {
		if (brId == null) {
			throw new NotFoundException("brId cannot be null");
		}

		Branch branch = branchCache.get(brId);
		if (branch == null) {
			synchronized (brId) {
				branch = branchCache.get(brId);
				if (branch == null) {
					branch = loadBranchById(brId);
					//放入部门缓存
					branchCache.put(brId, branch);
				}
			}
		}
		return branch;
	}

	/**
	 * 删除部门.
	 * 
	 * @param branch
	 *            要删除的部门.
	 */
	public void deleteBranch(Long brId) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("DELETE FROM app_branch where br_id = ? ");
			pstmt.setLong(1, brId);
			pstmt.execute();
		} catch (Exception e) {
			Log.error("删除部门出错!", e);
		} finally {
			DbConnectionManager.closeStatement(pstmt);
			DbConnectionManager.closeConnection(pstmt, con);
		}

		branchCache.remove(brId);
	}

	/**
	 * 根据 brId从数据库加载部门。
	 * 
	 * @param brId
	 *            brId
	 * @return 部门.
	 * 
	 * @throws 假如部门没有找到抛出
	 *             NotFoundException.
	 * 
	 */
	public Branch loadBranchById(Long brId) throws NotFoundException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con
					.prepareStatement("SELECT br_no,br_name,br_parent_id,br_status,remark FROM app_branch WHERE br_id = ? ");
			pstmt.setLong(1, brId);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new NotFoundException();
			}
			String brNo = rs.getString("br_no");
			String brName = rs.getString("br_name");
			Long brParentId = rs.getLong("br_parent_id");
			if (rs.wasNull()) {
				brParentId = null;
			}
			String brStatus = rs.getString("br_status");
			String brRemark = rs.getString("remark");

			Branch branch = new Branch();
			branch.setBrId(brId);
			branch.setBrNo(brNo);
			branch.setBrName(brName);
			branch.setBrParentId(brParentId);
			branch.setBrStatus(brStatus);
			branch.setBrRemark(brRemark);

			return branch;
		} catch (Exception e) {
			Log.error("查询部门信息时出错", e);
			throw new NotFoundException();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

	/**
	 * 根据  brNo 从数据库加载部门。
	 * 
	 * @param brNo
	 *            brNo
	 * @return 部门.
	 * 
	 * @throws 假如部门没有找到抛出
	 *             NotFoundException.
	 * 
	 */
	public Branch loadBranchByBrNo(String brNo) throws NotFoundException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con
					.prepareStatement("SELECT br_id,br_no,br_name,br_parent_id,br_status,remark FROM app_branch WHERE br_no = ? ");
			pstmt.setString(1, brNo);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new NotFoundException();
			}
			Long brId = rs.getLong("br_id");
			String brName = rs.getString("br_name");
			Long brParentId = rs.getLong("br_parent_id");
			if (rs.wasNull()) {
				brParentId = null;
			}
			String brStatus = rs.getString("br_status");
			String brRemark = rs.getString("remark");

			Branch branch = new Branch();
			branch.setBrId(brId);
			branch.setBrNo(brNo);
			branch.setBrName(brName);
			branch.setBrParentId(brParentId);
			branch.setBrStatus(brStatus);
			branch.setBrRemark(brRemark);

			return branch;
		} catch (Exception e) {
			Log.error("查询部门信息时出错", e);
			throw new NotFoundException();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

	/**
	 * 返回系统中所有的机构.
	 * 
	 * @return 机构集合.
	 */
	public List<Branch> getBranchs() {
		List<Branch> branchs = new ArrayList<Branch>(500);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("SELECT br_id FROM app_branch where 1=1 ");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				try {
					branchs.add(getBranch(rs.getLong(1)));
				} catch (NotFoundException e) {
					//忽略
				}
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return branchs;
	}

	/**
	 * 返回系统中所有的机构.
	 * 
	 * @return 机构集合.
	 */
	public List<Branch> getBranchs(int startIndex, int numResults) {
		List<Branch> branchs = new ArrayList<Branch>(500);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement("SELECT br_id FROM app_branch");
				DbConnectionManager.setFetchSize(pstmt, 500);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					try {
						branchs.add(getBranch(rs.getLong(1)));
					} catch (NotFoundException e) {
						//忽略
					}
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(con, "SELECT br_id FROM app_branch");
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
				rs = pstmt.executeQuery();
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					try {
						branchs.add(getBranch(rs.getLong(1)));
					} catch (NotFoundException e) {
						//忽略
					}
					count++;
				}
			}

		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return branchs;
	}

	/**
	 * 返回系统中所有的机构.
	 * 
	 * @return 机构集合.
	 */
	public List<Branch> findBranchs(Map<String, String> querys, int startIndex, int numResults) {
		List<Branch> branchs = new ArrayList<Branch>(500);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			StringBuilder sql = new StringBuilder(90);
			sql.append("SELECT br_id FROM app_branch WHERE 1=1 ");

			if (querys.containsKey("brParentId")) {
				sql.append(" AND br_parent_id = ? ");
			}
			if (querys.containsKey("brNo")) {
				sql.append(" AND br_no = ? ");
			}
			if (querys.containsKey("brName")) {
				sql.append(" AND br_name like ? ");
			}

			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement(sql.toString());

				int parameterIndex = 1;
				if (querys.containsKey("brParentId")) {
					pstmt.setLong(parameterIndex++, Long.valueOf(querys.get("brParentId")));
				}
				if (querys.containsKey("brNo")) {
					pstmt.setString(parameterIndex++, querys.get("brNo"));
				}
				if (querys.containsKey("brName")) {
					pstmt.setString(parameterIndex++, querys.get("brName"));
				}

				DbConnectionManager.setFetchSize(pstmt, 500);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					try {
						branchs.add(getBranch(rs.getLong(1)));
					} catch (NotFoundException e) {
						//忽略
					}
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(con, sql.toString());
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);

				int parameterIndex = 1;
				if (querys.containsKey("brParentId")) {
					pstmt.setLong(parameterIndex++, Long.valueOf(querys.get("brParentId")));
				}
				if (querys.containsKey("brNo")) {
					pstmt.setString(parameterIndex++, querys.get("brNo"));
				}
				if (querys.containsKey("brName")) {
					pstmt.setString(parameterIndex++, querys.get("brName"));
				}

				rs = pstmt.executeQuery();
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					try {
						branchs.add(getBranch(rs.getLong(1)));
					} catch (NotFoundException e) {
						//忽略
					}
					count++;
				}
			}

		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return branchs;
	}
	
	/**
	 * 获取部门总数
	 * @return 部门总数
	 */
	public int getBranchCount() {
		int count = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("SELECT COUNT(*) FROM app_branch");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			Log.error("获取部门总数出错！", e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return count;
	}

}
